﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Maticsoft.DBUtility;
using WindowsFormsApplication1.Common;
using MySql.Data.MySqlClient;
using Z.BulkOperations;
using Newtonsoft.Json;
using System.IO;

namespace WindowsFormsApplication1
{
    public partial class Form7 : Form
    {
        public Form7()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //SynHotelReportData();
            BatSaveDataTest();

            return;
            //DataTable dt = new DataTable();
            //dt.TableName = "BulkTest";


            //DataColumn column = new DataColumn("serviceid", typeof(long));
            //column.AutoIncrement = true;
            //dt.Columns.Add(column);
            //dt.Columns.Add(new DataColumn("gpstime", typeof(DateTime)));
            //dt.Columns.Add(new DataColumn("lat", typeof(Decimal)));
            //dt.Columns.Add(new DataColumn("lng", typeof(Decimal)));
            //dt.Columns.Add(new DataColumn("speed", typeof(Decimal)));
            //dt.Columns.Add(new DataColumn("altitude", typeof(int)));
            var dt = DbHelperSQL.QueryM("SELECT * FROM BulkTest ORDER BY NUM", 2).Tables[0];
            dt.TableName = "BulkTest";

            for (var i = 0; i < dt.Rows.Count; i++)
            {
                //if (i > 2 && i < 10)
                {
                    dt.Rows[i]["Name"] = "qqqq" + i;
                }
            }

            for (int d = 24; d < 26; d++)
            {

                dt.Rows.Add(new object[] { Guid.NewGuid(), "lws" + d, d * 100 });
            }

            string ConnectionString = @"server=192.168.2.16;database=HotelData;uid=sa;pwd=123456;Max Pool Size =150;Connect Timeout=600;";

            Stopwatch sw = new Stopwatch();
            using (DbConnection connection = new SqlConnection(ConnectionString))
            {
                

                connection.Open();
                sw.Start();
                var bulk = new BulkOperation(connection);
                bulk.AutoMapKeyName = "Id";
                //bulk.BulkInsert(dt);
                //bulk.BulkUpdate(dt);
                //bulk.BulkDelete(dt);
                bulk.BulkMerge(dt);
                //bulk.BulkSaveChanges(ds);
                //bulk.BulkSynchronize(dt);
                sw.Stop();
                Console.WriteLine("用时：" + sw.ElapsedMilliseconds.ToString());
                Console.Read();
            }

            MessageBox.Show("OK!");
        }

        private void SynHotelReportData()
        {
            var synTime = "";

            var lastUptInfoDt = DbHelperSQL.QueryM("select max(CreationTime) CreationTime,max(LastModificationTime) LastModificationTime from [HotelOrderEntryMainReports]", 2);

            if (lastUptInfoDt != null && lastUptInfoDt.Tables[0] != null)
            {
                var createTime = lastUptInfoDt.Tables[0].Rows[0]["CreationTime"];
                var lastModificationTime = lastUptInfoDt.Tables[0].Rows[0]["CreationTime"];

                var dt = DbHelperSQL.Query($"SELECT * FROM HotelOrderEntryMainReports WHERE [CreationTime]>'{createTime}' OR [lastModificationTime]>dateadd(mi,-1,'{lastModificationTime}')").Tables[0];
                dt.TableName = "HotelOrderEntryMainReports";

                string ConnectionString = @"server=192.168.2.16;database=fanjiahotelplatform;uid=sa;pwd=123456;Max Pool Size =150;Connect Timeout=600;";

                Stopwatch sw = new Stopwatch();
                using (DbConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    sw.Start();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    //bulk.BulkInsert(dt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                    sw.Stop();
                    Console.WriteLine("用时：" + sw.ElapsedMilliseconds.ToString());
                    Console.Read();
                }

                LogHelper.WriteLog($"同步完成，共{dt.Rows.Count}条记录。");

                MessageBox.Show($"同步完成，共{dt.Rows.Count}条记录。");
            }

            MessageBox.Show("OK!");
        }

        private void BatSaveDataTest()
        {
            var result = File.ReadAllText(@"d:\\meituan_hotelinfo.txt");
            var hotelAdds = JsonConvert.DeserializeObject<IEnumerable<MeituanHotelInfoEntity>>(result);

            string ConnectionString = @"Server=rm-uf6ovq4p2bv8tmi1p3o.mysql.rds.aliyuncs.com;Database=MeiTuanHotelData;User=duser01;Password=losdik28_0r;Port=3306;Allow User Variables=True;SslMode = none;AllowLoadLocalInfile=true;";

            Stopwatch sw = new Stopwatch();
            using (DbConnection connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();
                sw.Start();

                using (var bulk = new BulkOperation<MeituanHotelInfoEntity>(connection))
                {
                    // easy to use
                    bulk.DestinationTableName = "meituanhotelinfoadd";
                    bulk.BulkInsert(hotelAdds);
                }

                //var bulk = new BulkOperation(connection);
                //bulk.AutoMapKeyName = "Id";
                ////bulk.BulkInsert(dt);
                ////bulk.BulkUpdate(dt);
                ////bulk.BulkDelete(dt);
                //bulk.BulkMerge(dt);
                //bulk.BulkSaveChanges(ds);
                //bulk.BulkSynchronize(dt);
                sw.Stop();
                Console.WriteLine("用时：" + sw.ElapsedMilliseconds.ToString());
                Console.Read();
            }
          
            MessageBox.Show($"同步完成，共{hotelAdds.Count()}条记录。");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            TestMerge();
            //RefFrontImgBySupplier();
            //RefFrontImgByQianTaoSupplier();
            //RefFrontImgBySupplierRezen();
            //RefFrontImgBySupplierGrt();

            //RefFrontImgBySupplierRujia();

            //RefFrontImgBySupplierTiTanYun();

            //RefFrontImgBySupplierDesson();
            //RefFrontImgBySupplierYd();
            //RefFrontImgBySupplierHuaZhu();
        }

        private void RefFrontImgByProv()
        {
            //省份查询
            var provDt = DbHelperSQL.Query("SELECT * FROM Provinces ORDER BY Code WHERE Isdeleted =0")?.Tables[0];
            if (provDt != null && provDt.Rows.Count > 0)
            {
                foreach (DataRow provRow in provDt.Rows)
                {
                    //var cityDt = DbHelperSQL.Query($"SELECT * FROM Cities WHERE Isdeleted =0 AND ProvinceId='{provRow["Id"]}'")?.Tables[0];
                    //if (cityDt != null && cityDt.Rows.Count > 0)
                    //{
                    //    foreach (DataRow cityRow in cityDt.Rows)
                    //    {
                    //        var hotelDt = DbHelperSQL.Query($"SELECT [Id],[ThumbImageUrl] from HotelBasicInfoes WHERE CityId='{cityRow["Id"]}' AND [ThumbImageUrl] IS NULL")?.Tables[0];
                    //    }
                    //}

                    var hotelDt = DbHelperSQL.Query($@"select hotelid,SupplierHotelId,SupplierId
                        FROM[fanjiahotelplatform].[dbo].[HotelMappings]
                        where isdeleted = 0 and hotelid in (SELECT[Id] from HotelBasicInfoes WHERE CityId in (select id from Cities where [ProvinceId] = '{provRow["Id"]}') AND[ThumbImageUrl] IS NULL)")?.Tables[0];
                }
                
            }
        }

        private void RefFrontImgBySupplier()
        {
  //          var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
  //  FROM [fanjiahotelplatform].[dbo].[HotelMappings]
  //WHERE isdeleted=0 and SupplierId='B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE [ThumbImageUrl] IS NULL)")?.Tables[0];

            //var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

  //          try
  //          {
  //              using (DbConnection connection = new MySqlConnection(mysqlConnStr))
  //              {
  //                  hotelDt.TableName = "hotelfrontimg";
  //                  connection.Open();
  //                  var bulk = new BulkOperation(connection);
  //                  bulk.AutoMapKeyName = "Id";
  //                  bulk.BulkInsert(hotelDt);
  //                  //bulk.BulkUpdate(dt);
  //                  //bulk.BulkDelete(dt);
  //                  //bulk.BulkMerge(dt);
  //                  //bulk.BulkSaveChanges(ds);
  //                  //bulk.BulkSynchronize(dt);
  //              }

  //              DbHelperMySQL.ExecuteSql("update `hotelfrontimg` i left join meituanhotelinfo h on i.SupplierHotelId=h.HotelId set i.imgurl = h.FrontImage", mysqlConnStr);

  //              MessageBox.Show("OK");
  //          }
  //          catch (Exception e)
  //          {
  //              MessageBox.Show(e.Message);
  //          }

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {

                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierRezen()
        {
            //var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
            //  FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            //WHERE isdeleted=0 and SupplierId='a8375567-b63c-1b9d-f2bc-aa96d2605f53'")?.Tables[0];

            //var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

            //try
            //{
            //    using (DbConnection connection = new MySqlConnection(mysqlConnStr))
            //    {
            //        hotelDt.TableName = "hotelfrontimg";
            //        connection.Open();
            //        var bulk = new BulkOperation(connection);
            //        bulk.AutoMapKeyName = "Id";
            //        bulk.BulkInsert(hotelDt);
            //        //bulk.BulkUpdate(dt);
            //        //bulk.BulkDelete(dt);
            //        //bulk.BulkMerge(dt);
            //        //bulk.BulkSaveChanges(ds);
            //        //bulk.BulkSynchronize(dt);
            //    }

            //    DbHelperMySQL.ExecuteSqlWithConn("update `hotelfrontimg` i left join rezenhotelinfo h on i.SupplierHotelId=h.HotelId set i.imgurl = h.CoverImage", "MysqlConn");

            //    MessageBox.Show("OK");
            //}
            //catch (Exception e)
            //{
            //    MessageBox.Show(e.Message);
            //}

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }

                DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");
            }

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierGrt()
        {
  //          var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId
  //  FROM [fanjiahotelplatform].[dbo].[HotelMappings]
  //WHERE isdeleted=0 and SupplierId='d1d0c7f2-dc98-4b64-b36b-f6cd9e7fbb08' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

  //          var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

  //          try
  //          {
  //              using (DbConnection connection = new MySqlConnection(mysqlConnStr))
  //              {
  //                  hotelDt.TableName = "hotelfrontimg";
  //                  connection.Open();
  //                  var bulk = new BulkOperation(connection);
  //                  bulk.AutoMapKeyName = "Id";
  //                  bulk.BulkInsert(hotelDt);
  //                  //bulk.BulkUpdate(dt);
  //                  //bulk.BulkDelete(dt);
  //                  //bulk.BulkMerge(dt);
  //                  //bulk.BulkSaveChanges(ds);
  //                  //bulk.BulkSynchronize(dt);
  //              }

  //              DbHelperMySQL.ExecuteSqlWithConn("update `hotelfrontimg` i left join chinaonlinehotelinfo h on i.SupplierHotelId=h.HotelId set i.imgurl = h.FrontImage", "MysqlConn");

  //              MessageBox.Show("OK");
  //          }
  //          catch (Exception e)
  //          {
  //              MessageBox.Show(e.Message);
  //          }

  //          return;

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                try
                {
                    DbHelperSQL.ExecuteSqlM("TRUNCATE TABLE HotelFrontImg");

                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }

                //DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");
            }

            MessageBox.Show("OK");
        }

        private void RefFrontImgByQianTaoSupplier()
        {
            //var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
            //  FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            //WHERE isdeleted=0 and SupplierId='798bd348-9911-4db4-92ca-848bb0064ec0' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

            //try
            //{
            //    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString2))
            //    {
            //        hotelDt.TableName = "hotelfrontimg";
            //        connection.Open();
            //        var bulk = new BulkOperation(connection);
            //        bulk.AutoMapKeyName = "Id";
            //        bulk.BulkInsert(hotelDt);
            //        //bulk.BulkUpdate(dt);
            //        //bulk.BulkDelete(dt);
            //        //bulk.BulkMerge(dt);
            //        //bulk.BulkSaveChanges(ds);
            //        //bulk.BulkSynchronize(dt);
            //    }

            //    //DbHelperMySQL.ExecuteSql("update i set i.ImgUrl=d.[MainPicture] from [HotelFrontImg] i,[QiantaoHotelDetail] d where i.supplierhotelid=d.hotelcode", mysqlConnStr);

            //    MessageBox.Show("OK");
            //}
            //catch (Exception e)
            //{
            //    MessageBox.Show(e.Message);
            //}

            var rstDt = DbHelperSQL.QueryM("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE imgurl!='' and imgurl is not null", 2).Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }

            //  update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierRujia()
        {
            //var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
            //  FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            //WHERE isdeleted=0 and SupplierId='073a26b3-874c-4903-b787-6465ee23789b' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

            //try
            //{
            //    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString2))
            //    {
            //        hotelDt.TableName = "hotelfrontimg";
            //        connection.Open();
            //        var bulk = new BulkOperation(connection);
            //        bulk.AutoMapKeyName = "Id";
            //        bulk.BulkInsert(hotelDt);
            //        //bulk.BulkUpdate(dt);
            //        //bulk.BulkDelete(dt);
            //        //bulk.BulkMerge(dt);
            //        //bulk.BulkSaveChanges(ds);
            //        //bulk.BulkSynchronize(dt);
            //    }

            //    //DbHelperMySQL.ExecuteSql("update [HotelFrontImg] set ImgUrl=d.picurl from [HotelFrontImg] i,[RujiaHotelPicUrl] d where i.supplierhotelid=d.[HotelCd] and d. pictype=1", mysqlConnStr);
            //    //DbHelperMySQL.ExecuteSql("update [HotelFrontImg] set ImgUrl=d.s_Img from [HotelFrontImg] i,[RujiaHotelInfo] d where i.supplierhotelid=d.s_HotelCd and (i.ImgUrl='' or i.ImgUrl is null)", mysqlConnStr);

            //    MessageBox.Show("OK");
            //}
            //catch (Exception e)
            //{
            //    MessageBox.Show(e.Message);
            //}

            //return;

            var rstDt = DbHelperSQL.QueryM("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE imgurl!='' and imgurl is not null", 2).Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                DbHelperSQL.ExecuteSqlM("TRUNCATE TABLE HotelFrontImg");

                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }

            DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierTiTanYun()
        {
            var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
             FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            WHERE isdeleted=0 and SupplierId='8303eb05-ec9c-464b-93bb-5c3f2fa3cca3' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

            var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

            try
            {
                using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                {
                    hotelDt.TableName = "hotelfrontimg";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    bulk.BulkInsert(hotelDt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }

                DbHelperMySQL.ExecuteSqlWithConn("update `hotelfrontimg` i left join titancloudhotelinfo h on i.SupplierHotelId=h.HotelId set i.imgurl = h.AppearancePicUrl", "MysqlConn");

                //MessageBox.Show("OK");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL AND ImgUrl<>''", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                DbHelperSQL.ExecuteSqlM("TRUNCATE TABLE HotelFrontImg");

                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }

                DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");
            }

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierDesson()
        {
            var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
             FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            WHERE isdeleted=0 and SupplierId='557c7ab5-5816-4c1f-8aed-339a96177143' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

            var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

            try
            {
                using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                {
                    hotelDt.TableName = "hotelfrontimg";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    bulk.BulkInsert(hotelDt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }

                DbHelperMySQL.ExecuteSqlWithConn("update `hotelfrontimg` i left join dossenhotelinfo h on i.SupplierHotelId=h.HotelId set i.imgurl = h.Pic", "MysqlConn");

                //MessageBox.Show("OK");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL AND ImgUrl<>''", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                DbHelperSQL.ExecuteSqlM("TRUNCATE TABLE HotelFrontImg");

                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }

                DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");
            }

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierYd()
        {
            var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
             FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            WHERE isdeleted=0 and SupplierId='607b69c3-2dba-41b5-bcaf-10a014faf1dd' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

            var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

            try
            {
                using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                {
                    hotelDt.TableName = "hotelfrontimg";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    bulk.BulkInsert(hotelDt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }

                DbHelperMySQL.ExecuteSqlWithConn("update `hotelfrontimg` i left join chinaonlinehotelinfo h on i.SupplierHotelId=h.HotelId set i.imgurl = h.FrontImage", "MysqlConn");

                //MessageBox.Show("OK");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL AND ImgUrl<>''", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                DbHelperSQL.ExecuteSqlM("TRUNCATE TABLE HotelFrontImg");

                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }

                DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");
            }

            MessageBox.Show("OK");
        }

        private void RefFrontImgBySupplierHuaZhu()
        {
            var hotelDt = DbHelperSQL.Query(@"SELECT newid() Id,HotelId,SupplierHotelId,'' as ImgUrl
             FROM [fanjiahotelplatform].[dbo].[HotelMappings]
            WHERE isdeleted=0 and SupplierId='89e38e71-47d3-4946-92f4-b41e55ecd38f' and hotelid in(SELECT [Id] from HotelBasicInfoes WHERE isdeleted=0 AND [ThumbImageUrl] IS NULL)")?.Tables[0];

            var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

            try
            {
                using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                {
                    hotelDt.TableName = "hotelfrontimg";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    bulk.BulkInsert(hotelDt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }

                DbHelperMySQL.ExecuteSqlWithConn("update `hotelfrontimg` i left join huazhuhotelinfo h on i.SupplierHotelId=h.HotelNo set i.imgurl = h.Picture", "MysqlConn");

                //MessageBox.Show("OK");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            var rstDt = DbHelperMySQL.Query("SELECT Id,HotelId,ImgUrl FROM hotelfrontimg WHERE ImgUrl IS NOT NULL AND ImgUrl<>''", "MysqlConn").Tables[0];
            if (rstDt != null && rstDt.Rows.Count > 0)
            {
                DbHelperSQL.ExecuteSqlM("TRUNCATE TABLE HotelFrontImg");

                try
                {
                    using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString))
                    {
                        rstDt.TableName = "HotelFrontImg";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "Id";
                        bulk.BulkInsert(rstDt);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }

                DbHelperSQL.ExecuteSqlM("update HotelFrontImg set ImgUrl=ImgUrl+'.640-480.jpg'");
                DbHelperSQL.ExecuteSqlM("update b set b.[ThumbImageUrl]=i.ImgUrl from [dbo].[HotelBasicInfoes] b,HotelFrontImg i where i.hotelid=b.id");
            }

            MessageBox.Show("OK");
        }

        private void TestMerge()
        {
            var rstDt = DbHelperSQL.QueryM("SELECT Id,[StarRating],[BusinessDistrict],[HotelFacilities],[TotalScore],'AAA' OT FROM [HotelBasicInfoes] WHERE ID='5C199676-5475-4E24-BC7F-00000761A125'", 3).Tables[0];

            foreach (DataRow dr in rstDt.Rows)
            {
                dr["StarRating"] = 3;
                dr["TotalScore"] = 4.5;
            }

            try
            {
                using (DbConnection connection = new SqlConnection(DbHelperSQL.connectionString3))
                {
                    rstDt.TableName = "HotelBasicInfoes";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "Id";
                    //bulk.BulkInsert(rstDt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    bulk.BulkMerge(rstDt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            MessageBox.Show("OK!");
        }
    }
}
